package week_three.day02;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import week_three.day02.util.DBUtil;

public class TestConnection {
	private static Connection conn;

	@BeforeClass
	public static void init() throws Exception {

		conn = DBUtil.createConnection();
	}

	@AfterClass
	public static void destroy() throws Exception {
		conn.close();
	}

	@Test
	public void testInsert() throws Exception {
		String sql = "insert into customers1(ID,CNAME) values(?,?)";
		PreparedStatement pst = null;
		int m = 0;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, "1");
			pst.setString(2, "Alice");
			m = pst.executeUpdate();
			System.out.println(m);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, null);
		}
	}

	@Test
	public void testDelete() throws Exception {
		String sql = "delete customers1 where id=?";
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, 1);
			int m = pst.executeUpdate();
			pst.close();
			System.out.println(m);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, null);
		}
	}

	@Test
	public void testUpdate() throws Exception {
		String sql = "update customers1 set cname=? where id=?";
		PreparedStatement pst = null;
		int m = 0;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, "Alice2");
			pst.setInt(2, 2);
			m = pst.executeUpdate();
			pst.close();
			System.out.println(m);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, null);
		}
	}

	@Test
	public void testFindAll() throws Exception {
		String sql = "select * from customers1";
		PreparedStatement pst = null;
		ResultSet rSet = null;
		try {
			pst = conn.prepareStatement(sql);
			rSet = pst.executeQuery();
			while (rSet.next()) {
				String cnameString = rSet.getString("cname");
				int id = rSet.getInt("id");
				System.out.println(id + "\t" + cnameString);
			}
		} catch (Exception e) {
			DBUtil.close(null, pst, rSet);
		}

	}
}
